﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace SalaryChannel.Web
{
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    [ServiceBehavior(IncludeExceptionDetailInFaults = true)] 
    public class QueryService :ServiceAuthRoot, IQueryService
    {
        
        
        public bool DeleteAll(string StatisMon)
        {
             int ret=-1;
            try
            {
                Authoricate();
                using (DataClassesQueryDataContext context = new DataClassesQueryDataContext())
                {
                   ret = context.SP_DeleteSalary(StatisMon);
                }
                if (ret == 0)
                {
                    return true;
                }
                LogManager.Write("SP_DeleteSalary失败，返回" + ret.ToString());
                return false;
            }
            catch (Exception ex)
            {
                LogManager.Write("DeleteAll: "+ ex.Message);
                return false;
            }

        }


        
        public List<string> GetStatisMon()
        {
            try
            {
                Authoricate();
                using (DataClassesQueryDataContext context = new DataClassesQueryDataContext())
                {
                    var query = (from p in context.Salary_Mon
                                 orderby p.统计月份 descending
                                 select p.统计月份
                                  ).Distinct();
                    var q2 = (from p in context.Salary_Mon_201108
                        orderby p.统计月份 descending
                                 select p.统计月份
                                  ).Distinct();
                    List<string> ret = new List<string>();
                    ret.AddRange(query.ToList<string>());
                    ret.AddRange(q2.ToList<string>());

                    return ret;
                }
            }
            catch (Exception ex)
            {
                LogManager.Write("获取统计月份失败:" + ex.Message + "--" + HttpContext.Current.Request.UserHostAddress);
                return null;
            }
        }

        
        public string ImportData(List<string> dataRows, string TableName)
        {
            try
            {
                Authoricate();
                using (DataClassesQueryDataContext context = new DataClassesQueryDataContext())
                {
                    if (TableName.Equals("Salary_Mon"))
                    {
                        foreach (string row in dataRows)
                        {
                            //LogManager.Write(row);
                            string[] col = row.Split((char)9);//tab
                            Salary_Mon_201108 smon = new Salary_Mon_201108();
                            //smon.统计月份 = cols[0];
                            //smon.渠道编号 = cols[1];
                            //smon.本月任务 = cols[2];
                            //smon.全球通新增_户 = cols[3];
                            //smon.动感地带新增_户 = cols[4];
                            //smon.神州行新增_户 = cols[5];
                            //smon.随E行八十元套餐新增_户 = cols[6];
                            //smon.商务新增_户 = cols[7];
                            //smon.家园卡2010版新增_户 = cols[8];
                            //smon.创业一百新增_户 = cols[9];
                            //smon.其他创业卡新增_户 = cols[10];
                            //smon.新增合计_户 = cols[11];
                            //smon.新增酬金合计_元 = cols[12];
                            //smon.净增数_户 = cols[13];
                            //smon.净增酬金_元 = cols[14];
                            //smon.代收话费酬金_元 = cols[15];
                            //smon.售卡酬金_元 = cols[16];
                            //smon.新赠机_元 = cols[17];
                            //smon.老赠机_元 = cols[18];
                            //smon.承建费_元 = cols[19];
                            //smon.代收百分之二_元 = cols[20];
                            //smon.岁末_元 = cols[21];
                            //smon.酬金合计_元 = cols[22];
                            //smon.离网率 = cols[23];
                            //smon.考核得分 = cols[24];
                            //smon.考核后酬金_元 = cols[25];
                            //smon.自购终端加百分之一提成_元 = cols[26];
                            //smon.房屋补贴_元 = cols[27];
                            //smon.补发_元 = cols[28];
                            //smon.店员积分奖励_元 = cols[29];
                            //smon.本月应发酬金_元 = cols[30];
                            //smon.扣税_元 = cols[31];
                            //smon.扣除酬金池金额_元 = cols[32];
                            //smon.本月实发_元 = cols[33];
                            smon.统计月份 = col[0];
                            smon.渠道编号 = col[1];
                            smon.任务 = col[2];
                            smon.新增 = col[3];
                            smon.得分 = col[4];
                            smon.一返 = col[5];
                            smon.一返酬金 = col[6];
                            smon.一返应发 = col[7];
                            smon.二返 = col[8];
                            smon.二返酬金 = col[9];
                            smon.代收酬金 = col[10];
                            smon.空充酬金 = col[11];
                            smon.有价卡 = col[12];
                            smon.店员积分 = col[13];
                            smon.基础酬金 = col[14];
                            smon.超额奖励 = col[15];
                            smon.服务扣罚 = col[16];
                            smon.自购终端提成 = col[17];
                            smon.房屋补贴 = col[18];
                            smon.数据业务体验店终端补助 = col[19];
                            smon.数据业务体验店无线网卡补助 = col[20];
                            smon.手机缴费通酬金 = col[21];
                            smon.补发 = col[22];
                            smon.奖励扣补合计 = col[23];
                            smon.考核后酬金 = col[24];
                            smon.上月扣补 = col[25];
                            smon.其他酬金1 = col[26];
                            smon.其他酬金2 = col[27];
                            smon.其他酬金3 = col[28];
                            smon.本月合计 = col[29];
                            smon.活动酬金 = col[30];
                            smon.总税金 = col[31];
                            smon.酬金池当月扣发 = col[32];
                            smon.本月实发 = col[33];

                            context.Salary_Mon_201108.InsertOnSubmit(smon);
                        }

                    }
                    else if (TableName == "Salary_Pool")
                    {
                        foreach (string row in dataRows)
                        {
                            string[] cols = row.Split((char)9);//tab
                            Salary_Pool sp = new Salary_Pool();
                            sp.统计月份 = cols[0];
                            sp.渠道编号 = cols[1];
                            sp.本月渠道扣除金额_元 = cols[2];
                            sp.本月公司补贴金额_元 = cols[3];
                            sp.本月酬金池合计_元 = cols[4];

                            context.Salary_Pool.InsertOnSubmit(sp);

                        }

                    }
                    context.SubmitChanges();
                    return "true";
                }
            }
            catch (Exception ex)
            {
                LogManager.Write("导入数据失败：" + ex.Message + "--" + HttpContext.Current.Request.UserHostAddress);
                return ex.Message;
            }
        }



        public XDataTable QuerySalaryMonByID( string StatisMon, string ChannelID,string UserName)
        {
            try
            {
                Authoricate();
                #region old0206
                //using (DataClassesQueryDataContext context = new DataClassesQueryDataContext())
                //{
                //    var auth = from a in context.UserInfor
                //               where a.User_ID == UserID
                //               select a;
                //    if (!auth.Any())
                //    {
                //        throw new Exception("用户不存在：" + UserID);
                //    }
                //    string UserName = string.Format("[{0}]{1}", UserID, auth.First().UserName);
                //    if (auth.First().AdminLevel == 1 )
                //    {//超级管理员
                //        if (string.IsNullOrEmpty(ChannelID))
                //        {
                //            return (from q in context.VW_SalaryMon
                //                    where q.统计月份 == StatisMon
                //                    select q).ToList<VW_SalaryMon>();
                //        }

                //        return (from q in context.VW_SalaryMon
                //                where q.统计月份 == StatisMon
                //                where q.渠道编号 == ChannelID
                //                select q).ToList<VW_SalaryMon>();
                //    }
                //    //普通用户


                //    var query = from p in context.VW_SalaryMon
                //                where p.渠道编号 == auth.First().Channel_ID
                //                where p.统计月份 == StatisMon
                //                select p;

                //    if (!query.Any())
                //    {
                //        return new List<VW_SalaryMon>();
                //    }
                //    string areaName = query.First().县区;

                //    lock (this)
                //    {
                //        QueryLog ql = new QueryLog();
                //        ql.StatisMonth = StatisMon;
                //        ql.ChannelID = auth.First().Channel_ID;
                //        ql.QueryName = UserName;
                //        ql.IP = HttpContext.Current.Request.UserHostAddress;
                //        ql.QueryTime = DateTime.Now;

                //        context.QueryLog.InsertOnSubmit(ql);
                //        context.SubmitChanges();
                //    }

                //    return query.ToList<VW_SalaryMon>();
                //}
                #endregion
                using(SqlConnection con = new SqlConnection(this.SqlString))
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "SP_GetSalaryByMonth";

                    cmd.Parameters.AddRange(new SqlParameter[]
                    {
                        new SqlParameter("@StatisMonth",StatisMon),
                        new SqlParameter("@ChannelID",ChannelID)
                    });

                    DataTable table = new DataTable("SalaryMonth");
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(table);
                    try
                    {
                        if (!string.IsNullOrEmpty(ChannelID))
                        {
                            using (DataClassesQueryDataContext context = new DataClassesQueryDataContext())
                            {
                                lock (this)
                                {
                                    QueryLog ql = new QueryLog();
                                    ql.StatisMonth = StatisMon;
                                    ql.ChannelID = ChannelID;
                                    ql.QueryName = UserName;
                                    ql.IP = HttpContext.Current.Request.UserHostAddress;
                                    ql.QueryTime = DateTime.Now;

                                    context.QueryLog.InsertOnSubmit(ql);
                                    context.SubmitChanges();
                                }
                            }
                        }
                    }
                    catch(Exception e1)
                    {
                        
                        LogManager.Write("查询酬金后插入QueryLog失败，"+ e1.Message);
                    }

                    return new XDataTable(table);

                }

            }
            catch (Exception e)
            {
                LogManager.Write("查询月薪酬失败："  + "--" + e.Message + "--" + HttpContext.Current.Request.UserHostAddress);
                return null ;
            }
        }

        
        public List<VW_SalaryPool> GetSalaryPool(string ID, string StatisMon, string ChannelID)
        {
            try
            {
                Authoricate();
                using (DataClassesQueryDataContext context = new DataClassesQueryDataContext())
                {
                    var auth = from a in context.UserInfor
                               where a.User_ID == ID
                               select a;
                    if (auth.First().AdminLevel == 1)
                    {
                        if (string.IsNullOrEmpty(ChannelID))
                        {
                            return (from q in context.VW_SalaryPool
                                    where q.统计月份 == StatisMon
                                    select q).ToList<VW_SalaryPool>();

                        }

                        return (from q in context.VW_SalaryPool
                                where q.统计月份 == StatisMon
                                where q.渠道编号 == ChannelID
                                select q).ToList<VW_SalaryPool>();

                    }
                    var query = from p in context.VW_SalaryPool
                                where p.渠道编号 == auth.First().Channel_ID
                                where p.统计月份 == StatisMon
                                select p;
                    return query.ToList<VW_SalaryPool>();
                }
            }
            catch (Exception e)
            {
                LogManager.Write("查询月酬金池失败：" + ID + "--" + e.Message + "--" + HttpContext.Current.Request.UserHostAddress);
                return null;
            }
        }



        public List<QueryLogTable> GetQueryLogTable(string StatisMon)
        {
            try
            {
                Authoricate();
                List<QueryLogTable> result = new List<QueryLogTable>();
                int ChannelTotal = 0;
                int QueryTotal = 0;
                int ValidTotal = 0;
                float ValidPTotal = 0F;

                DataClassesQueryDataContext context = new DataClassesQueryDataContext();


                foreach (SP_QueryStatis_个结果 dr in context.SP_QueryStatis(StatisMon))
                {
                    QueryLogTable ql = new QueryLogTable
                    {
                        AreaName = dr.CountyName,
                        ChannelCount = (int)dr.ChannelCount,
                        QueryCount = (int)dr.QueryCount,
                        ValidChannelCount = (int)dr.ValidQueryCount,
                        ValidP = string.Format("{0:P2}", (float)dr.ValidPer)
                    };
                    ChannelTotal += ql.ChannelCount;
                    QueryTotal += ql.QueryCount;
                    ValidTotal += ql.ValidChannelCount;

                    result.Add(ql);

                }
                ValidPTotal =ChannelTotal==0 ? 0F : (float)ValidTotal / (float)ChannelTotal;
                result.Add(new QueryLogTable
                {
                    AreaName = "总计",
                    ChannelCount = ChannelTotal,
                    QueryCount = QueryTotal,
                    ValidChannelCount = ValidTotal,
                    ValidP = string.Format("{0:P2}", ValidPTotal)
                });

                context.Dispose();
                return result;
            }
            catch (Exception ex)
            {
                LogManager.Write("统计使用情况失败:" + StatisMon + "--" + ex.Message + "--" + HttpContext.Current.Request.UserHostAddress);
                return new List<QueryLogTable>();
            }
        }


    }

    public class QueryLogTable
    {
        public string AreaName
        { get; set; }
        public int ChannelCount
        { get; set; }
        public int QueryCount
        { get; set; }
        public int ValidChannelCount
        {
            set;
            get;
        }
        public string ValidP
        {
            get;
            set;
        }
    }
}